/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Entidad.Muestra;
import Frontera.FMuestrasCustodia;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Juan
 */

public class MuestraDAO {
    
    private static final String SQL_INSERT =
            "INSERT INTO muestras ("
            + "solicitudes_consecutivo_de_solicitud, clientes_abreviatura, codigo_alpha, identificacion, cantidad, observaciones, "
            + "radioactiva, magnetica, tipo_disposicion, almacenada, dispuesta, sobrantes_devueltos"
            + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String SQL_SELECT =
            "SELECT * "
            + "FROM muestras where codigo_alpha= ? AND clientes_abreviatura = ?";
    private static final String SQL_SELECT_All = 
            "SELECT * "
            + "FROM muestras";
    private static final String SQL_UPDATE =
            "UPDATE muestras SET "
            + "identificacion = ?, cantidad = ?, observaciones = ?, "
            + "radioactiva = ?, magnetica = ?, tipo_disposicion = ?, almacenada = ?, dispuesta = ?, sobrantes_devueltos = ?"
            + " WHERE "
            + "codigo_alpha = ? AND clientes_abreviatura = ?";
    private static final String SQL_DELETE =
            "DELETE FROM muestras WHERE "
            +" radioactiva = ? AND clientes_abreviatura = ?";
    
    private static final String SELECCIONAR_NO_ALMACENADAS =
            "SELECT * FROM muestras WHERE "
            + "almacenada = FALSE and radioactiva = FALSE";
    
    private static final String SELECCIONAR_RADIOACTIVAS =
            "SELECT * FROM muestras WHERE "
            + "dispuesta = FALSE AND radioactiva = TRUE";
    
    private static final String SELECCIONAR_DEVOLUCION_SOBRANTES = 
            "SELECT * FROM muestras WHERE "
            + "almacenada = TRUE AND sobrantes_devueltos = FALSE AND radioactiva = FALSE";
    
    private static final String SELECCIONAR_DEVOLUCION_MUESTRA =
            "SELECT * FROM muestras WHERE "
            + "almacenada = TRUE AND dispuesta = FALSE AND radioactiva = FALSE AND solicitudes_consecutivo_de_solicitud IN (%s)";

      public List BusquedaUnCriterio(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        try {
            ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ " = ?");
            setSuitableMethod(1, ps, valores.get(0));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
       public List BusquedaDosCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        try {
            ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
      
   public List BusquedaTresCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);    
        }
   }
   public List BusquedaCuatroCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
   public List BusquedaCincoCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
   public List BusquedaSeisCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
    public List BusquedaSieteCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        criterio7 = criterios.get(6);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ? AND "+criterio7+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            setSuitableMethod(7, ps, valores.get(6));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
    
     public List BusquedaOchoCriterios(ArrayList <String> criterios, ArrayList <Object> valores, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        criterio1 = criterios.get(0);
        criterio2 = criterios.get(1);
        criterio3 = criterios.get(2);
        criterio4 = criterios.get(3);
        criterio5 = criterios.get(4);
        criterio6 = criterios.get(5);
        criterio7 = criterios.get(6);
        criterio8 = criterios.get(7);
        try {
           ps = conn.prepareStatement("SELECT * FROM muestras WHERE "+criterio1+ "= ? AND "+criterio2+ "= ? AND "+criterio3+ "= ? AND "+criterio4+ "= ? AND "+criterio5+ "= ? AND "+criterio6+ "= ? AND "+criterio7+ "= ? AND "+criterio8+ "= ?");
            setSuitableMethod(1, ps, valores.get(0));
            setSuitableMethod(2, ps, valores.get(1));
            setSuitableMethod(3, ps, valores.get(2));
            setSuitableMethod(4, ps, valores.get(3));
            setSuitableMethod(5, ps, valores.get(4));
            setSuitableMethod(6, ps, valores.get(5));
            setSuitableMethod(7, ps, valores.get(6));
            setSuitableMethod(8, ps, valores.get(7));
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }
    
    private static final String SHOW_TABLE_STATUS =
            "SHOW TABLE STATUS LIKE 'muestras'";

    public int create(Muestra muestra, Connection conexion) throws SQLException {

        PreparedStatement ps = null;
        try {
            int IDMuestra = siguienteIDMuestra(conexion);
            
            ps = conexion.prepareStatement(SQL_INSERT);
            ps.setInt(1, muestra.getConsecutivoSolicitud());
            ps.setString(2, muestra.getAbreviaturaCliente());
            ps.setInt(3, muestra.getCodigoAlpha());
            ps.setString(4, muestra.getIdentificacion());
            ps.setDouble(5,muestra.getCantidad());
            ps.setString(6, muestra.getObservaciones());
            ps.setBoolean(7,muestra.isRadioactiva());
            ps.setBoolean(8,muestra.isMagnetica());
            ps.setInt(9, muestra.getTipoDisposicion());
            ps.setBoolean(10, muestra.isAlmacenada());
            ps.setBoolean(11, muestra.isDevuelta());
            ps.setBoolean(12, muestra.isSobrantesDevueltos());
           
            ps.executeUpdate();
            return IDMuestra;
        } finally {
            cerrar(ps);
        }
    }

    public Muestra load(Muestra mu, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT);
            ps.setInt(1, mu.getCodigoAlpha());
            ps.setString(2, mu.getAbreviaturaCliente());
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return (Muestra)results.get(0);
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
   }

    public List loadAll(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
    }

    public void update(Muestra mu, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_UPDATE);
            ps.setString(1, mu.getIdentificacion());
            ps.setDouble(2, mu.getCantidad());
            ps.setString(3, mu.getObservaciones());
            ps.setBoolean(4,mu.isRadioactiva());
            ps.setBoolean(5, mu.isMagnetica());
            ps.setInt(6, mu.getTipoDisposicion());
            ps.setBoolean(7, mu.isAlmacenada());
            ps.setBoolean(8, mu.isDevuelta());
            ps.setBoolean(9, mu.isSobrantesDevueltos());
            ps.setInt(10, mu.getCodigoAlpha());
            ps.setString(11, mu.getAbreviaturaCliente());
            ps.executeUpdate();
            
        } finally {
            cerrar(ps);
        }
    }

    public void delete(Muestra mu, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_DELETE);
            ps.setInt(1, mu.getCodigoAlpha());
            ps.setString(2, mu.getAbreviaturaCliente());
            ps.executeUpdate();
        } finally {
            cerrar(ps);
        }
    }

    private List getResults(ResultSet rs) throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            Muestra muestra = new Muestra();
            muestra.setIdMuestra(rs.getInt("id_muestra"));
            muestra.setAbreviaturaCliente(rs.getString("clientes_abreviatura"));
            muestra.setConsecutivoSolicitud(rs.getInt("solicitudes_consecutivo_de_solicitud"));
            muestra.setCodigoAlpha(rs.getInt("codigo_alpha"));
            muestra.setIdentificacion(rs.getString("identificacion"));
            muestra.setCantidad(rs.getDouble("cantidad"));
            muestra.setObservaciones(rs.getString("observaciones"));
            muestra.setRadioactiva(rs.getBoolean("radioactiva"));
            muestra.setMagnetica(rs.getBoolean("magnetica"));
            muestra.setTipoDisposicion(rs.getInt("tipo_disposicion"));
            muestra.setAlmacenada(rs.getBoolean("almacenada"));
            muestra.setDevuelta(rs.getBoolean("dispuesta"));
            muestra.setSobrantesDevueltos(rs.getBoolean("sobrantes_devueltos"));
            
            results.add(muestra);
        }
        return results;
    }
    
    public int siguienteIDMuestra(Connection conexion) throws SQLException{
        int resultado;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conexion.prepareStatement(SHOW_TABLE_STATUS);
            rs = ps.executeQuery();
            if (rs.first()) {
                resultado = rs.getInt("Auto_increment");
            } else {
                resultado = -1;
            }
            return resultado;
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
    }
    public void setSuitableMethod(int indice, PreparedStatement ps, Object obj){
        try {
            switch(obj.getClass().getName()){
                  case "java.lang.String": ps.setString(indice, obj.toString().trim());break;
                  case "java.lang.Integer": ps.setInt(indice, Integer.parseInt(obj.toString()));break;
                  case "java.lang.Boolean": ps.setBoolean(indice, Boolean.parseBoolean(obj.toString().trim()));break;   
            } 
            
        } catch (SQLException | NumberFormatException ex){
        }   
    }
    
    private PreparedStatement muestrasDisposicion(Connection conexion) throws Exception {
        int [] numerosSolicitud = new SolicitudDAO().solicitudesHaceTresMeses(conexion);
        if (numerosSolicitud != null) {
            String numeros = "";
            for (int i = 0; i < numerosSolicitud.length; i++) {
                numeros += numerosSolicitud[i]+",";
            }
            String sentencia = String.format(SELECCIONAR_DEVOLUCION_MUESTRA, numeros.substring(0, numeros.length() - 1));
            return conexion.prepareStatement(sentencia);
        } else {
            throw new Exception("No hay muestras que deban ser devueltas.");
        }
    }
    
    public ArrayList<Muestra> muestrasCustodia(int tipoBusqueda, Connection conexion) throws Exception{
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (tipoBusqueda == FMuestrasCustodia.ALMACENAMIENTO)
                ps = conexion.prepareStatement(SELECCIONAR_NO_ALMACENADAS);
            else if (tipoBusqueda == FMuestrasCustodia.RADIOACTIVAS)
                ps = conexion.prepareStatement(SELECCIONAR_RADIOACTIVAS);
            else if (tipoBusqueda == FMuestrasCustodia.DEVOLUCION_O_DISPOSICION_MUESTRA) {
                ps = muestrasDisposicion(conexion);
            } else
                ps = conexion.prepareStatement(SELECCIONAR_DEVOLUCION_SOBRANTES);
            
            rs = ps.executeQuery();
            return (ArrayList<Muestra>) getResults(rs);
        } finally {
            cerrarRs(rs);
            cerrar(ps);
        }
    }

    private void cerrar(PreparedStatement ps) throws SQLException {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrarRs(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
    }

    public void cerrarCnn(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
            }
        }
    } 
    private static  String criterio1;
    private static  String criterio2;
    private static  String criterio3;
    private static  String criterio4;
    private static  String criterio5;
    private static  String criterio6;
    private static  String criterio7;
    private static  String criterio8;
}
